﻿using System;
using System.IO;
using System.Web;
using System.Web.UI;
using NPOI.HSSF.UserModel;

/*using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
using System.Text;
using Microsoft.CSharp;*/

namespace Voluntariar.WEB.Util
{

    public class ExcelClass
    {
        int WorkSheet = 1;
        HSSFWorkbook templateWorkbook = null;
        HSSFSheet sheet = null;
        String NomePlanilha = string.Empty;

        public ExcelClass(String Caminho, String Nome, String Formato)
        {
            NomePlanilha = Nome;

            FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(Caminho + Nome + ".xls"), FileMode.Open, FileAccess.Read);

            templateWorkbook = new HSSFWorkbook(fs, true);

            setWorkSheet(WorkSheet);
        }

        public string RetornaLetraPorIndex(int index)
        {

            String[] letras = { "0","A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
                                          "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};

            String celula = "";
            if (index <= 26)
            {
                celula = letras[index];
            }
            else if (index > 26)
            {
                int quociente = (index / 26);
                int resto = index % 26;

                celula = letras[quociente] + letras[resto];
            }
            return celula;
        }

        public void setWorkSheet(int planilha)
        {
            try
            {
                planilha--;
                sheet = templateWorkbook.GetSheetAt(planilha);
                WorkSheet = planilha;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Mesclar Colunas
        /// </summary>
        /// <param name="linha">Linha</param>
        /// <param name="colunaInicio">Coluna Inicial</param>
        /// <param name="colunaFim">Coluna Final</param>
        public void MesclarColuna(int linha, int colunaInicio, int colunaFim)
        {
            linha--;
            colunaInicio--;
            colunaFim--;
            NPOI.HSSF.Util.CellRangeAddress cra = new NPOI.HSSF.Util.CellRangeAddress(linha, linha, colunaInicio, colunaFim);
            sheet.AddMergedRegion(cra);
        }

        /// <summary>
        /// Mesclar Linhas
        /// </summary>
        /// <param name="coluna">Coluna</param>
        /// <param name="linhaInicio">Linha Inicial</param>
        /// <param name="linhaFim">Linha Final</param>
        public void MesclarLinha(int coluna, int linhaInicio, int linhaFim)
        {
            coluna--;
            linhaInicio--;
            linhaFim--;
            NPOI.HSSF.Util.CellRangeAddress cra = new NPOI.HSSF.Util.CellRangeAddress(linhaInicio, linhaFim, coluna, coluna);
            sheet.AddMergedRegion(cra);
        }

        /// <summary>
        /// Cor da fonte da celula
        /// </summary>
        /// <param name="linha">Linha</param>
        /// <param name="coluna">Coluna</param>
        /// <param name="cor">NPOI.HSSF.Util.HSSFColor.RED.index</param>
        public void CorFonte(int linha, int coluna, short cor)
        {
            coluna--;
            linha--;
            HSSFFont f1 = templateWorkbook.CreateFont();
            f1.Color = cor;

            HSSFRow dataRow = sheet.GetRow(linha);
            if (dataRow == null)
            {
                sheet.CreateRow(linha);
                dataRow = sheet.GetRow(linha);
            }

            if (dataRow.GetCell(coluna) == null)
                CriarCelula(dataRow, coluna);

            dataRow.GetCell(coluna).RichStringCellValue.ApplyFont(f1);
        }

        public void EditandoExcel(int linha, int coluna, string FonteNome = "",
                                  short FontColor = 0, short border = 0, bool Negrito = false,
                                  short Alignment = 0, short CorCelula = 0, bool DataFormat = false)
        {
            coluna--;
            linha--;
            HSSFCellStyle style = templateWorkbook.CreateCellStyle();
            HSSFFont f1 = templateWorkbook.CreateFont();
            style.WrapText = true;

            if (DataFormat)
            {
                HSSFDataFormat format = templateWorkbook.CreateDataFormat();
                style.DataFormat = format.GetFormat("R$  #,##0.00_);(R$  #,##0.00)");
            }

            if (!FonteNome.Equals(string.Empty))
                f1.FontName = FonteNome;
            if (!FontColor.Equals(0))
                f1.Color = FontColor;
            if (!border.Equals(0))
            {
                style.BorderBottom = border;
                style.BorderLeft = border;
                style.BorderRight = border;
                style.BorderTop = border;
            }
            if (!Alignment.Equals(0))
                style.Alignment = Alignment;
            if (!CorCelula.Equals(0))
            {
                style.FillForegroundColor = CorCelula;
                style.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
            }
            if (Negrito)
            {
                f1.Boldweight = 700;
                f1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            }


            HSSFRow dataRow = sheet.GetRow(linha);
            if (dataRow == null)
            {
                sheet.CreateRow(linha);
                dataRow = sheet.GetRow(linha);
            }

            if (dataRow.GetCell(coluna) == null)
                CriarCelula(dataRow, coluna);

            style.SetFont(f1);
            dataRow.GetCell(coluna).CellStyle = style;
            //dataRow.GetCell(coluna).RichStringCellValue.ApplyFont(f1);
        }

        public void FonteNegrito(int linha, int coluna)
        {
            coluna--;
            linha--;
            HSSFFont f1 = templateWorkbook.CreateFont();
            //f1.Boldweight = negrito;
            f1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            HSSFRow dataRow = sheet.GetRow(linha);
            if (dataRow == null)
            {
                sheet.CreateRow(linha);
                dataRow = sheet.GetRow(linha);
            }

            if (dataRow.GetCell(coluna) == null)
                CriarCelula(dataRow, coluna);


            HSSFCellStyle estilo = templateWorkbook.CreateCellStyle();
            estilo.SetFont(f1);
            dataRow.GetCell(coluna).RichStringCellValue.ApplyFont(f1);
        }

        /// <summary>
        /// Cor de fundo da celula
        /// </summary>
        /// <param name="linha">Linha</param>
        /// <param name="coluna">Coluna</param>
        /// <param name="cor">NPOI.HSSF.Util.HSSFColor.RED.index</param>
        public void CorCelula(int linha, int coluna, short cor)
        {
            coluna--;
            linha--;
            HSSFCellStyle style = templateWorkbook.CreateCellStyle();
            style.FillForegroundColor = cor;
            style.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;

            HSSFRow dataRow = sheet.GetRow(linha);
            if (dataRow == null)
            {
                sheet.CreateRow(linha);
                dataRow = sheet.GetRow(linha);
            }

            if (dataRow.GetCell(coluna) == null)
                CriarCelula(dataRow, coluna);

            dataRow.GetCell(coluna).CellStyle = style;
        }

        public int getIndiceWorkSheet()
        {
            return WorkSheet;
        }

        public HSSFSheet getSheet()
        {
            return this.sheet;
        }

        private void CriarCelula(HSSFRow dataRow, int Coluna)
        {
            dataRow.CreateCell(Coluna);
        }

        public void setAnoHeader(int ano)
        {
            try
            {
                sheet.Header.Left = sheet.Header.Left.Replace("@ano", ano.ToString());
                sheet.Header.Center = sheet.Header.Center.Replace("@ano", ano.ToString());
                sheet.Header.Right = HSSFHeader.Page;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setReplaceHeader(string antigo, string novo)
        {
            try
            {
                sheet.Header.Left = sheet.Header.Left.Replace(antigo, novo);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setReplaceFooter(string antigo, string novo)
        {
            try
            {
                sheet.Footer.Left = sheet.Header.Left.Replace(antigo, novo);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setData()
        {
            try
            {
                sheet.Footer.Left = sheet.Footer.Left.Replace("@data", DateTime.Now.ToString("dd/MM/yyyy"));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setConteudoCelula(int Linha, int Coluna, String Conteudo)
        {
            try
            {
                if (Linha == 0 || Coluna == 0)
                    return;
                Linha--;
                Coluna--;

                HSSFRow dataRow = sheet.GetRow(Linha);

                if (dataRow == null)
                {
                    sheet.CreateRow(Linha);
                    dataRow = sheet.GetRow(Linha);
                }

                if (dataRow.GetCell(Coluna) == null)
                    CriarCelula(dataRow, Coluna);


                dataRow.GetCell(Coluna).SetCellValue(Conteudo);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setConteudoCelula(int Linha, int Coluna, double Valor)
        {
            try
            {
                if (Linha == 0 || Coluna == 0)
                    return;
                Linha--;
                Coluna--;

                HSSFRow dataRow = sheet.GetRow(Linha);
                if (dataRow == null)
                {
                    sheet.CreateRow(Linha);
                    dataRow = sheet.GetRow(Linha);
                }

                if (dataRow.GetCell(Coluna) == null)
                    CriarCelula(dataRow, Coluna);

                dataRow.GetCell(Coluna).SetCellValue(Valor);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setConteudoCelula(int Linha, int Coluna, int Valor)
        {
            try
            {
                if (Linha == 0 || Coluna == 0)
                    return;
                Linha--;
                Coluna--;

                HSSFRow dataRow = sheet.GetRow(Linha);
                if (dataRow == null)
                {
                    sheet.CreateRow(Linha);
                    dataRow = sheet.GetRow(Linha);
                }

                if (dataRow.GetCell(Coluna) == null)
                    CriarCelula(dataRow, Coluna);

                dataRow.GetCell(Coluna).SetCellValue(Valor);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setFormulaCelula(int Linha, int Coluna, String Formula)
        {
            try
            {
                Linha--;
                Coluna--;

                HSSFRow dataRow = sheet.GetRow(Linha);

                if (dataRow.GetCell(Coluna) == null)
                    CriarCelula(dataRow, Coluna);

                dataRow.GetCell(Coluna).SetCellFormula(Formula);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void AbrirExcel(Page pagina)
        {
            try
            {
                sheet.ForceFormulaRecalculation = true;

                MemoryStream ms = new MemoryStream();

                templateWorkbook.Write(ms);

                byte[] byteArray = ms.ToArray();

                ms.Flush();
                ms.Close();

                /*HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + NomePlanilha + ".xls\"");
                HttpContext.Current.Response.AddHeader("Content-Length", byteArray.Length.ToString());
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.BinaryWrite(byteArray); */

                Voluntariar.WEB.Util.Sessao.ArquivoDownload = byteArray;
                Voluntariar.WEB.Util.Sessao.NomeArquivoDownload = NomePlanilha + ".xls";
                string script = "window.open('/Util/Download.aspx','_blank');";
                ScriptManager.RegisterStartupScript(pagina, pagina.GetType(), "download" + Guid.NewGuid().ToString().GetHashCode().ToString("x"), script, true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public byte[] getByteArray()
        {
            try
            {
                sheet.ForceFormulaRecalculation = true;

                MemoryStream ms = new MemoryStream();

                templateWorkbook.Write(ms);

                byte[] byteArray = ms.ToArray();

                ms.Flush();
                ms.Close();

                return byteArray;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //MÉTODOS A SEREM CRIADOS COM A NPOI
        public void ApagarLinhas(String Inicio, String Fim)
        {
            try
            {
                //Excel.Range range = workSheet.get_Range(Inicio, Fim);
                //range.EntireRow.Delete(Excel.XlDirection.xlUp);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InserirLinha(int Indice)
        {
            try
            {

                sheet.CreateRow(Indice);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void setFormulaCelula(String Celula, String Formula)
        {
            try
            {
                //Excel.Range range = workSheet.get_Range(Celula);
                //.Formula = Formula;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ExcluirExcel()
        {
            //File.Delete(CaminhoPlanilha);
        }
    }

    /*
    public class ExcelClass
    {
        #region Atributos / Propriedades

        int IndiceWorkSheet = 1;
        Excel.Application app = null;
        Excel.Workbook workbook = null;
        Excel.Worksheet workSheet = null;
        Excel.Range workSheet_range = null;
        String CaminhoPlanilha;
        #endregion

        #region Construtor

        public ExcelClass(String Caminho, String Nome, String Formato)
        {
            app = new Excel.Application();
            String Origem = HttpContext.Current.Server.MapPath(Caminho + Nome + Formato);

            String NomeTemporario = Nome + "_" + DateTime.Now.Ticks.ToString() + Formato;
            this.CaminhoPlanilha = HttpContext.Current.Server.MapPath(Caminho + "TEMP/" + NomeTemporario);

            File.Copy(Origem, this.CaminhoPlanilha);

            workbook = app.Workbooks.Open(this.CaminhoPlanilha, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            setWorkSheet(IndiceWorkSheet);
        }

        #endregion

        public void setWorkSheet(int Indice)
        {
            try
            {
                workSheet = (Excel.Worksheet)workbook.Sheets[Indice];
                IndiceWorkSheet = Indice;
            }
            catch (Exception theException)
            {

            }
        }

        public int getIndiceWorkSheet()
        {
            return IndiceWorkSheet;
        }

        public void setConteudoCelula(int Linha, int Coluna, String Conteudo)
        {
            try
            {
                if (Linha == 0 || Coluna == 0)
                    return;
                workSheet.Cells[Linha, Coluna] = Conteudo;

            }
            catch (Exception theException)
            {

            }
        }

        public void setConteudoCelula(int Linha, int Coluna, double Valor)
        {
            try
            {
                if (Linha == 0 || Coluna == 0)
                    return;
                workSheet.Cells[Linha, Coluna] = Valor;

            }
            catch (Exception theException)
            {

            }
        }

        public void setFormulaCelula(String Celula, String Formula)
        {
            try
            {
                Excel.Range range = workSheet.get_Range(Celula);
                range.Formula = Formula;
            }
            catch (Exception theException)
            {

            }
        }

        public void ApagarLinhas(String Inicio, String Fim)
        {
            try
            {
                Excel.Range range = workSheet.get_Range(Inicio, Fim);
                range.EntireRow.Delete(Excel.XlDirection.xlUp);
            }
            catch (Exception theException)
            {

            }
        }

        public void InserirLinha(int Indice)
        {
            try
            {
                Excel.Range range = workSheet.get_Range("A" + Indice.ToString()).EntireRow;
                range.Insert(-4121);
            }
            catch (Exception theException)
            {

            }
        }

        private void AdicionarConteudoPlanilha(int row, int col, string dados, string cell1, string cell2, string format)
        {
            if (col.Equals(6))
                workSheet.Cells[row, col] = Convert.ToDecimal(dados);
            else
                workSheet.Cells[row, col] = dados;

            workSheet_range = workSheet.get_Range(cell1, cell2);
            //workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            //workSheet_range.NumberFormat = string.Empty;
        }

        public void AbrirExcel()
        {
            workbook.Save();
            workbook.Close();
            //app.Visible = true;
            //app.UserControl = true;
            FileInfo fInfo = new FileInfo(CaminhoPlanilha);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + fInfo.Name + "\"");
            HttpContext.Current.Response.AddHeader("Content-Length", fInfo.Length.ToString());
            HttpContext.Current.Response.Flush();
            //HttpContext.Current.Response.WriteFile(fInfo.FullName);
            HttpContext.Current.Response.TransmitFile(fInfo.FullName);
            HttpContext.Current.Response.Flush();
            fInfo.Delete();
            HttpContext.Current.Response.End();
            fInfo = null;
        }

        public void ExcluirExcel()
        {
            File.Delete(CaminhoPlanilha);
        }
    } */
}
